﻿using System;
using System.ComponentModel;
using System.Data;
using System.ServiceModel.Activation;
using UFIDA.U9.WSS.CommonService.Interfaces;
using UFIDA.U9.WSS.CommonService.Models;
using UFIDA.U9.WSS.DebugService;
using UFIDA.U9.WSS.ProxyService.Models;
using UFSoft.UBF.Business;
using UFSoft.UBF.PL;
using UFSoft.UBF.Sys.Database;
using UFSoft.UBF.Util.Context;
using UFSoft.UBF.Util.DataAccess;
using UFSoft.UBF.View.Query;

namespace UFIDA.U9.WSS.CommonService.Services
{
    /// <summary>
    ///     普通服务
    /// </summary>
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class CommonService : ICommonService
    {
        /// <summary>
        ///     测试连接
        /// </summary>
        /// <returns></returns>
        [Description("测试连接")]
        public ReturnMessage<bool> TestConnection()
        {
            ReturnMessage<bool> ret = new ReturnMessage<bool>();
            ret.Data = true;
            return ret;
        }

        #region 其它

        /// <summary>
        ///     通过Oql获取Sql
        /// </summary>
        /// <param name="oql"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        private static string ConvertOqlToSql(string oql, ref DataParamList dataParams)
        {
            string selectStatement;
            try
            {
                QueryContext queryContext = new QueryContext();
                ObjectQuery objectQuery = queryContext.DefineQuery(oql);
                CompiledQuery compiledQuery = objectQuery.CompiledQuery;
                OQLTools.MemoryEvaluate(compiledQuery.MemoryEvaluations, dataParams);
                selectStatement = compiledQuery.SelectStatement;
            }
            catch
            {
                throw new CommonQueryException("OQL解析不正确");
            }

            return selectStatement;
        }

        #endregion

        #region 实体查询

        [Description("查询实体")]
        public ReturnMessage<QueryResult<DataTable>> QueryEntity(EntityQueryCond queryCond,
            PageParamter pageParamter)
        {
            if (queryCond == null)
                throw new CommonQueryException("queryCond is null");
            if (string.IsNullOrWhiteSpace(queryCond.EntityFullName))
                throw new CommonQueryException("queryCond.EntityFullName cannot be empty");
            string oql = string.Format("select {0} from {1} where {2} {3}",
                string.IsNullOrWhiteSpace(queryCond.SelectClause) ? "*" : queryCond.SelectClause,
                queryCond.EntityFullName.Replace(".", "::"),
                string.IsNullOrWhiteSpace(queryCond.WhereClause) ? "1 = 1" : queryCond.WhereClause,
                string.IsNullOrWhiteSpace(queryCond.OrderByClause)
                    ? string.Empty
                    : " order by " + queryCond.OrderByClause);
            return QueryEntityByOQL(oql, pageParamter);
        }

        [Description("通过OQL查询")]
        public ReturnMessage<QueryResult<DataTable>> QueryEntityByOQL(string oql, PageParamter pageParamter)
        {
            if (string.IsNullOrWhiteSpace(oql))
                throw new CommonQueryException("oql cannot be empty");
            QueryResult<DataTable> queryResult = new QueryResult<DataTable>();
            queryResult.PageParamter = pageParamter;
            DataSet dataSet;
            if (pageParamter == null)
            {
                EntityDataQuery entityDataQuery = new EntityDataQuery(string.Empty);
                entityDataQuery.QOptions.MultiLangOption = new GlobalizationContext(PlatformContext.Current.Culture);
                dataSet = entityDataQuery.FindDataSet(oql);
            }
            else
            {
                if (pageParamter.PageIndex < 0) pageParamter.PageIndex = 0;
                if (pageParamter.PageSize < 0) pageParamter.PageSize = 0;
                //通过Oql获取sql
                DataParamList dataParams = new DataParamList();
                string sql = ConvertOqlToSql(oql, ref dataParams);
                pageParamter.TotalCount = GetRecordCountBySqlImpl(sql);
                pageParamter.TotalPage = pageParamter.PageSize == 0
                    ? 0
                    : (long) Math.Ceiling((decimal) pageParamter.TotalCount / pageParamter.PageSize);
                //获取分页DataSet
                dataSet = GetDataSetByPage(sql, pageParamter.PageIndex * pageParamter.PageSize, pageParamter.PageSize);
            }

            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                DataTable table = dataSet.Tables[0];
                foreach (DataColumn column in table.Columns)
                {
                    if (column.ColumnName.StartsWith("A_"))
                        column.ColumnName = column.ColumnName.Substring(2);
                }

                queryResult.QueryData = table;
            }

            ReturnMessage<QueryResult<DataTable>> ret = new ReturnMessage<QueryResult<DataTable>>();
            ret.Data = queryResult;
            return ret;
        }

        [Description("获取实体数量")]
        public ReturnMessage<long> GetEntityCount(string entityFullName, string whereClause)
        {
            if (string.IsNullOrWhiteSpace(entityFullName))
                throw new CommonQueryException("entityFullName cannot be empty");
            ReturnMessage<long> ret = new ReturnMessage<long>();
            ret.Data = GetEntityCountImpl(entityFullName, whereClause);
            return ret;
        }

        [Description("实体是否存在")]
        public ReturnMessage<bool> EntityExists(string entityFullName, string whereClause)
        {
            if (string.IsNullOrWhiteSpace(entityFullName))
                throw new CommonQueryException("entityFullName cannot be empty");
            ReturnMessage<bool> ret = new ReturnMessage<bool>();
            EntityDataQuery entityDataQuery = new EntityDataQuery(entityFullName);
            ret.Data = entityDataQuery.IsExists(whereClause);
            return ret;
        }

        #endregion

        #region SQL查询

        [Description("通过SQL查询")]
        public ReturnMessage<QueryResult<DataTable>> QueryBySQL(string sql, PageParamter pageParamter)
        {
            if (string.IsNullOrWhiteSpace(sql))
                throw new CommonQueryException("sql cannot be empty");
            QueryResult<DataTable> queryResult = new QueryResult<DataTable>();
            queryResult.PageParamter = pageParamter;
            DataSet dataSet;
            if (pageParamter == null)
            {
                DataAccessor.RunSQL(DatabaseManager.GetCurrentConnection(), sql, null, out dataSet);
            }
            else
            {
                if (pageParamter.PageIndex < 0) pageParamter.PageIndex = 0;
                if (pageParamter.PageSize < 0) pageParamter.PageSize = 0;
                pageParamter.TotalCount = GetRecordCountBySqlImpl(sql);
                pageParamter.TotalPage = pageParamter.PageSize == 0
                    ? 0
                    : (long) Math.Ceiling((decimal) pageParamter.TotalCount / pageParamter.PageSize);
                //获取分页DataSet
                dataSet = GetDataSetByPage(sql, pageParamter.PageIndex * pageParamter.PageSize, pageParamter.PageSize);
            }

            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                DataTable table = dataSet.Tables[0];
                queryResult.QueryData = table;
            }

            ReturnMessage<QueryResult<DataTable>> ret = new ReturnMessage<QueryResult<DataTable>>();
            ret.Data = queryResult;
            return ret;
        }

        [Description("通过SQL获取记录数量")]
        public ReturnMessage<long> GetRecordCountBySQL(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql))
                throw new CommonQueryException("sql cannot be empty");
            ReturnMessage<long> ret = new ReturnMessage<long>();
            ret.Data = GetRecordCountBySqlImpl(sql);
            return ret;
        }

        #endregion

        #region 分页查询

        /// <summary>
        ///     获取分页DataSet
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static DataSet GetDataSetByPage(string sql, long startRowIndex, long pageSize)
        {
            string selectsql;
            string wheresql;
            string ordersql;
            string tableName;
            //重建Sql分页
            ReBuildSqlForPage(ref pageSize, ref sql, out selectsql, out wheresql, out ordersql, out tableName);
            return GetDataSetByPage(tableName, pageSize, startRowIndex, selectsql, wheresql, ordersql, "", "*", null);
        }

        /// <summary>
        ///     获取分页DataSet
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="selectSql"></param>
        /// <param name="whereSql"></param>
        /// <param name="orderbySql"></param>
        /// <param name="optimizesql"></param>
        /// <param name="aliasFileds"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        private static DataSet GetDataSetByPage(string tableName, long pageSize, long startRowIndex, string selectSql,
            string whereSql, string orderbySql, string optimizesql, string aliasFileds, DataParamList dataParams)
        {
            string sql = CombineSqlForPage(tableName, pageSize, startRowIndex, selectSql, whereSql, orderbySql,
                aliasFileds);
            if (optimizesql != string.Empty)
            {
                sql = "with A as (" + sql + ") " + optimizesql;
            }

            DataSet dataSet;
            DataAccessor.RunSQL(DatabaseManager.GetCurrentConnection(), sql, dataParams, out dataSet);
            return dataSet;
        }

        /// <summary>
        ///     重建Sql分页
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="selectsql"></param>
        /// <param name="wheresql"></param>
        /// <param name="ordersql"></param>
        /// <param name="tableName"></param>
        private static void ReBuildSqlForPage(ref long pageSize, ref string sql, out string selectsql,
            out string wheresql, out string ordersql, out string tableName)
        {
            int fromIndex = sql.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 1;
            int whereIndex = sql.IndexOf(" where ", StringComparison.Ordinal) + 1;
            int orderByIndex = sql.IndexOf(" order by ", StringComparison.Ordinal) + 1;
            if (whereIndex == 0)
            {
                if (orderByIndex > 0)
                {
                    sql = sql.Substring(0, orderByIndex) + " where 1=1 " + sql.Substring(orderByIndex);
                }
                else
                {
                    sql += " where 1=1";
                }
            }

            whereIndex = sql.IndexOf(" where ", StringComparison.Ordinal) + 1;
            if (orderByIndex == 0)
            {
                sql += " order by A.[ID]";
            }

            orderByIndex = sql.IndexOf(" order by ", StringComparison.Ordinal) + 1;
            selectsql = sql.Substring(7, fromIndex - 8);
            wheresql = sql.Substring(whereIndex + 6, orderByIndex - whereIndex - 7);
            ordersql = sql.Substring(orderByIndex + 9);
            tableName = sql.Substring(fromIndex + 5, whereIndex - fromIndex - 6);
        }

        /// <summary>
        ///     组合Sql分页
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="selectSql"></param>
        /// <param name="whereSql"></param>
        /// <param name="orderbySql"></param>
        /// <param name="aliasFileds"></param>
        /// <returns></returns>
        private static string CombineSqlForPage(string tableName, long pageSize, long startRowIndex, string selectSql,
            string whereSql, string orderbySql, string aliasFileds)
        {
            switch (pageSize)
            {
                case -1:
                    return "SELECT " + aliasFileds + " ,__Qry_RowNumber FROM (SELECT " + selectSql +
                           ", ROW_NUMBER() OVER(ORDER BY " + orderbySql + ") AS __Qry_RowNumber  FROM " + tableName +
                           " WHERE " +
                           whereSql + ") T";
                case 0:
                    throw new CommonQueryException(" pagesize 不能为 0 ");
                default:
                    long endRowIndex = startRowIndex + pageSize;
                    return "SELECT " + aliasFileds + " FROM ( SELECT " + selectSql + " , ROW_NUMBER() OVER(ORDER BY " +
                           orderbySql + " ) AS __Qry_RowNumber  FROM " + tableName + " WHERE " + whereSql +
                           ") T WHERE T.__Qry_RowNumber> " + startRowIndex + "  and T.__Qry_RowNumber<= " + endRowIndex;
            }
        }

        #endregion

        #region 获取实体数量

        /// <summary>
        ///     获取实体数量
        /// </summary>
        /// <param name="entityFullName"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        private static long GetEntityCountImpl(string entityFullName, string whereClause)
        {
            EntityDataQuery entityDataQuery = new EntityDataQuery(entityFullName);
            whereClause = string.IsNullOrWhiteSpace(whereClause) ? "1=1" : whereClause;
            object objValue =
                entityDataQuery.FindValue("select count(ID) from " + entityFullName.Replace(".", "::") + " where " +
                                          whereClause);
            return objValue == DBNull.Value || objValue == null ? 0 : long.Parse(objValue.ToString());
        }

        /// <summary>
        ///     获取记录数量
        /// </summary>
        /// <param name="oql"></param>
        /// <returns></returns>
        private static long GetRecordCountByOql(string oql)
        {
            DataParamList dataParams = new DataParamList();
            string sql = ConvertOqlToSql(oql, ref dataParams);
            sql = GetRecordCountSQL(sql, dataParams);
            return GetRecordCountBySqlImpl(sql);
        }

        /// <summary>
        ///     获取记录数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static int GetRecordCountBySqlImpl(string sql)
        {
            DataParamList dataParams = new DataParamList();
            sql = GetRecordCountSQL(sql, dataParams);
            try
            {
                object obj;
                DataAccessor.RunSQL(DatabaseManager.GetCurrentConnection(), sql, dataParams, out obj);
                return (int) obj;
            }
            catch
            {
                throw new CommonQueryException(string.Format("语句执行异常:{0}", sql));
            }
        }

        /// <summary>
        ///     获取记录数量Sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        private static string GetRecordCountSQL(string sql, DataParamList dataParams)
        {
            int orderByIndex = sql.IndexOf(" order by ", StringComparison.OrdinalIgnoreCase);
            if (orderByIndex > 0)
                sql = sql.Substring(0, orderByIndex);
            return string.Format("select count(*) as recordCount from ({0}) as A", sql);
        }

        #endregion
    }
}